package cn.kbblog.dao;

import java.io.Closeable;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;



/**
 * 本类主要用于执行sql的CURD以及存储过程的操作和执行
 */
@Component
public class BaseDao implements Closeable {

	private static Logger LOG = LoggerFactory.getLogger(BaseDao.class);
	
	@Resource
	private JdbcTemplate jdbcTemplate;


	/**
	 * 根据SQL返回SQL执行后的结果集，以List的形式返回，查询多条数据
	 * @param sql SQL语句
	 * @param params 参数
	 * @return
	 */
	public List<Map<String, Object>> selectAll(String sql,Object... params) {
		try{
			return this.jdbcTemplate.queryForList(sql,params);
		}catch(Exception e){
			LOG.debug("SQL:"+sql);
			LOG.error(e.getMessage(),e);
			return null;
		}
	}

	/**
	 * 根据SQL返回SQL执行后的结果集，以Map的形式返回，查询一条数据
	 * @param sql SQL语句
	 * @param params 参数
	 * @return
	 */
	public Map<String,Object> selectOne(String sql,Object... params) {
		try{
			return this.jdbcTemplate.queryForMap(sql,params);
		}catch(EmptyResultDataAccessException e){
			LOG.debug("Empty Result");
		}catch(Exception e){
			LOG.debug("SQL:"+sql);
			LOG.error(e.getMessage(),e);
		}
		return null;
	}

	/**
	 *	执行更新SQL语句
	 * @param sql SQL语句
	 * @param params 参数
	 * @return
	 */
	public int update(String sql,Object... params) {
			return this.jdbcTemplate.update(sql,params);
	}

	/**
	 * 执行存储过程查询返回多张表的内容（主要针对mysql/sqlserver数据库，oracle数据库不支持返回值）
	 * 
	 * @param procName 存储过程名称
	 * @param objects 参数
	 * @return List ： 多张表集合 List < 单张表 List < 行 Map < 列名 String , 值 Object >>>
	 */
	public List<List<Map<String,Object>>> executeProc(String procName,Object...objects){
		List<List<Map<String,Object>>> result = null;
		StringBuilder sqlBuffer=new StringBuilder("{call  ").append(procName).append("(");
		for (int i = 0; i < objects.length; i++) {
			if(i>0)
				sqlBuffer.append(",?");
			else 
				sqlBuffer.append("?");
		}
		sqlBuffer.append(")}");
		try(Connection conn=this.jdbcTemplate.getDataSource().getConnection();CallableStatement cst=conn.prepareCall(sqlBuffer.toString());){
			for (int i = 0; i < objects.length; i++) {
				cst.setObject(i+1,objects[i]);
			}
			if (cst.execute()) {
				result = new ArrayList<List<Map<String,Object>>>();
				do {
					List<Map<String,Object>> tables=new ArrayList<Map<String,Object>>();
					try(ResultSet rs= cst.getResultSet();){
						ResultSetMetaData rsmd = rs.getMetaData();
						int colCount = rsmd.getColumnCount();
						while (rs.next()) {
							Map<String,Object> map=new HashMap<String,Object>();
							for (int j = 1; j <= colCount; j++)
								map.put(rsmd.getColumnLabel(j), rs.getObject(j));
							tables.add(map);
						}
						result.add(tables);
					} catch (SQLException e) {
						CREATE_PROC_LOG(e,procName,objects);
					}
				} while (cst.getMoreResults());
			}
		}catch(SQLException e){
			CREATE_PROC_LOG(e,procName,objects);
		}
		return result;
	}
	
	/**
	 * 存储过程执行失败日志记录的方法
	 * 
	 * @param e
	 * @param procName
	 * @param objects
	 */
	private static void CREATE_PROC_LOG(Exception e,String procName,Object... objects){
		StringBuilder errinfo=new StringBuilder(procName);
		errinfo.append("执行失败。存储过程参数：");
		for (Object obj : objects) errinfo.append(obj).append(" | ");
		LOG.error(errinfo.toString());
		LOG.error(e.getMessage(),e);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void close() throws IOException {
	}

	/**
	 * 执行sql server 分页查询
	 * @param sql
	 * @param pageSize
	 * @param page
	 * @param objects
	 * @return
	 */
	public List<Map<String, Object>> selectPageBySqlServer(String sql,Integer page, Integer pageSize,Object... objects) {
		pageSize = pageSize ==null?10:pageSize;
		page = page ==null?1:page;
		Integer start = pageSize*(page-1)+1,end=pageSize*page;
		sql = sql.replaceFirst("(?i)select", "select top("+end+") ");
		if(page ==1) return jdbcTemplate.queryForList(sql, objects);
		sql="with query as ( select inner_query.*, row_number() over (order by current_timestamp) as page_rn from ( "+sql+" ) inner_query ) select * from query where page_rn between "+start+" and "+end;
		return jdbcTemplate.queryForList(sql, objects);
	}
	
	/**
	 * 执行oracle 分页查询
	* @param sql
	* @param page
	* @param pageSize
	* @param objects
	* @return
	* List<Map<String,Object>>
	* @author ykb yang.kb@topcheer.com   
	* @date 2017年5月31日 下午1:30:12
	 */
	public List<Map<String, Object>> selectPageByOracle(String sql,Integer page, Integer pageSize,Object... objects) {
		pageSize = pageSize ==null?10:pageSize;
		page = page ==null?1:page;
		Integer start = pageSize*(page-1)+1,end=pageSize*page;
		sql = "SELECT * FROM  (  SELECT beeltT.*, ROWNUM beetl_rn FROM ( \n" + sql + "\n )  beeltT  WHERE ROWNUM <" + end +")  WHERE beetl_rn >= " + start;
		return jdbcTemplate.queryForList(sql, objects);
	}
	
	public List<Map<String, Object>> selectPageByMysql(String sql,Integer page, Integer pageSize,Object... objects) {
		pageSize = pageSize ==null?10:pageSize;
		page = page ==null?1:page;
		Integer start = pageSize*(page-1)+1,end=pageSize*page;
		sql = sql + " limit "+(start-1) +","+pageSize;//mysql数据库分页从0开始
		return jdbcTemplate.queryForList(sql, objects);
	}
	
	
}
